package dao;

import model.StudentApplication;
import util.DBUtil;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentApplicationDao {

    /**
     * 提交申请
     * @param studentId 学生学号
     * @param scholarshipName 奖学金名称
     * @param reason 申请理由
     * @return 成功提交返回true，失败返回false（奖学金不存在或已申请过）
     */
    public boolean submitApplicationWithReason(String studentId, String scholarshipName, String reason) {
        // 先通过奖学金名称查找对应的奖学金ID
        String findIdSql = "SELECT id FROM scholarship WHERE name = ?";
        // 检查该学生是否已经申请过此奖学金
        String checkSql = "SELECT 1 FROM applications WHERE student_id = ? AND scholarship_id = ?";
        // 插入申请记录，默认状态为“待审核”，申请时间为当前时间
        String insertSql = "INSERT INTO applications(student_id, scholarship_id, reason, status, apply_time) VALUES (?, ?, ?, '待审核', NOW())";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement psFind = conn.prepareStatement(findIdSql)) {

            // 1. 获取奖学金ID
            psFind.setString(1, scholarshipName);
            ResultSet rsFind = psFind.executeQuery();
            if (!rsFind.next()) {
                // 奖学金不存在，返回失败
                return false;
            }
            int scholarshipId = rsFind.getInt("id");

            // 2. 检查是否重复申请
            try (PreparedStatement psCheck = conn.prepareStatement(checkSql)) {
                psCheck.setString(1, studentId);
                psCheck.setInt(2, scholarshipId);
                ResultSet rsCheck = psCheck.executeQuery();
                if (rsCheck.next()) {
                    // 已申请过，返回失败
                    return false;
                }
            }

            // 3. 插入申请记录
            try (PreparedStatement psInsert = conn.prepareStatement(insertSql)) {
                psInsert.setString(1, studentId);
                psInsert.setInt(2, scholarshipId);
                psInsert.setString(3, reason);
                return psInsert.executeUpdate() > 0;  // 执行成功返回true
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return false; // 异常或失败返回false
    }

    /**
     * 撤销申请，只能撤销状态为“待审核”的申请
     * @param applicationId 申请记录ID
     * @return 撤销成功返回true，否则返回false
     */
    public boolean withdrawApplication(int applicationId) {
        // 删除条件：指定ID且状态是“待审核”的申请
        String sql = "DELETE FROM applications WHERE id = ? AND status = '待审核'";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, applicationId);
            return stmt.executeUpdate() > 0; // 删除成功返回true
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 获取某学生所有申请记录
     * @param studentId 学生学号
     * @return 返回该学生申请列表（List<StudentApplication>）
     */
    public List<StudentApplication> getApplicationList(String studentId) {
        List<StudentApplication> list = new ArrayList<>();
        // 查询申请记录，关联奖学金表获取奖学金名称，按申请时间降序排列
        String sql = "SELECT a.id, a.student_id, s.name AS scholarship_name, a.status, a.reason " +
                "FROM applications a " +
                "JOIN scholarship s ON a.scholarship_id = s.id " +
                "WHERE a.student_id = ? ORDER BY a.apply_time DESC";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, studentId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                // 将每条结果封装成 StudentApplication 对象，加入列表
                StudentApplication app = new StudentApplication();
                app.setId(rs.getInt("id"));
                app.setStudentId(rs.getString("student_id"));
                app.setScholarshipName(rs.getString("scholarship_name"));
                app.setStatus(rs.getString("status"));
                app.setReason(rs.getString("reason"));

                list.add(app);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    /**
     * 提交申请（带理由+上传文件）
     * @param studentId 学生学号
     * @param scholarshipName 奖学金名称
     * @param reason 申请理由
     * @param selectedFile 上传的文件
     * @return 成功返回true，否则false
     */
    public boolean submitApplicationWithReasonAndFile(String studentId, String scholarshipName, String reason, File selectedFile) {
        // 先判断文件是否存在
        if (selectedFile == null || !selectedFile.exists()) {
            System.err.println("文件不存在");
            return false;
        }

        String findIdSql = "SELECT id FROM scholarship WHERE name = ?";
        String checkSql = "SELECT 1 FROM applications WHERE student_id = ? AND scholarship_id = ?";
        // 新增了 file_path 字段，记录上传文件路径
        String insertSql = "INSERT INTO applications(student_id, scholarship_id, reason, status, file_path, apply_time) VALUES (?, ?, ?, '待审核', ?, NOW())";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement psFind = conn.prepareStatement(findIdSql)) {

            // 1. 查找奖学金ID
            psFind.setString(1, scholarshipName);
            ResultSet rsFind = psFind.executeQuery();
            if (!rsFind.next()) {
                System.err.println("奖学金不存在");
                return false;
            }
            int scholarshipId = rsFind.getInt("id");

            // 2. 检查是否重复申请
            try (PreparedStatement psCheck = conn.prepareStatement(checkSql)) {
                psCheck.setString(1, studentId);
                psCheck.setInt(2, scholarshipId);
                ResultSet rsCheck = psCheck.executeQuery();
                if (rsCheck.next()) {
                    System.err.println("已申请过该奖学金");
                    return false;
                }
            }

            // 3. 将文件复制上传到服务器指定目录（相对程序目录下的 uploads 文件夹）
            File uploadDir = new File("uploads");
            if (!uploadDir.exists()) {
                uploadDir.mkdirs();
            }
            // 生成唯一文件名防止重名，格式：时间戳_原文件名
            String fileName = System.currentTimeMillis() + "_" + selectedFile.getName();
            File destFile = new File(uploadDir, fileName);
            java.nio.file.Files.copy(selectedFile.toPath(), destFile.toPath());

            // 4. 插入申请记录，包括文件路径
            try (PreparedStatement psInsert = conn.prepareStatement(insertSql)) {
                psInsert.setString(1, studentId);
                psInsert.setInt(2, scholarshipId);
                psInsert.setString(3, reason);
                psInsert.setString(4, destFile.getAbsolutePath());  // 文件路径存数据库
                return psInsert.executeUpdate() > 0;  // 插入成功返回true
            }

        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
}
